package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import simorg.domain.Role;
import simorg.domain.User;
import simorg.util.DatabaseConnection;
import simorg.util.PasswordHash;

public class UserTable {

	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS USER";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS USER" +
		    	" (ID INT AUTO_INCREMENT PRIMARY KEY, USERNAME VARCHAR(20), PASSWORD VARCHAR(256), EMAIL VARCHAR(50), " + 
		    	"FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), ROLE VARCHAR(20))";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		   
		    User admin = new User("admin", Role.Admin);
		    admin.setPassword(PasswordHash.getHash("admin"));
		    admin.setEmail("sfrees@ramapo.edu");
		    admin.setFirstName("Scott");
		    admin.setLastName("Frees");
		    UserTable.save(dc, admin);
		    
		    User instructor = new User("InstructorA", Role.Instructor);
		    instructor.setPassword(PasswordHash.getHash("simorg"));
		    instructor.setEmail("instructorA@ramapo.edu");
		    instructor.setFirstName("Instructor");
		    instructor.setLastName("A");
		    UserTable.save(dc, instructor);
		    
		    instructor = new User("InstructorB", Role.Instructor);
		    instructor.setPassword(PasswordHash.getHash("simorg"));
		    instructor.setEmail("instructorB@ramapo.edu");
		    instructor.setFirstName("Instructor");
		    instructor.setLastName("B");
		    UserTable.save(dc, instructor);
		    
		    instructor = new User("InstructorC", Role.Instructor);
		    instructor.setPassword(PasswordHash.getHash("simorg"));
		    instructor.setEmail("instructorC@ramapo.edu");
		    instructor.setFirstName("Instructor");
		    instructor.setLastName("C");
		    UserTable.save(dc, instructor);
		    
		    User student = new User("StudentA", Role.Student);
		    student.setPassword(PasswordHash.getHash("simorg"));
		    student.setEmail("studentA@ramapo.edu");
		    student.setFirstName("Student");
		    student.setLastName("A");
		    UserTable.save(dc, student);
		    
		    student = new User("StudentB", Role.Student);
		    student.setPassword(PasswordHash.getHash("simorg"));
		    student.setEmail("studentB@ramapo.edu");
		    student.setFirstName("Student");
		    student.setLastName("B");
		    UserTable.save(dc, student);
		    
		    student = new User("StudentC", Role.Student);
		    student.setPassword(PasswordHash.getHash("simorg"));
		    student.setEmail("studentC@ramapo.edu");
		    student.setFirstName("Student");
		    student.setLastName("C");
		    UserTable.save(dc, student);
		    
		    System.out.println("Bootstrapped Users");
		    Collection<User> users = findAll(dc);
		    for ( User u : users ) {
		    	System.out.println("ID:  " + u.getId() + " - " + u.getUsername());
		    }
		}
		finally {
			dc.getConnection().close();
		}
	}

	public static void save(DatabaseConnection dc, User user) {
		try {
			String query = null;
			if ( user.getId() < 0 ) {
				query = "INSERT INTO USER (USERNAME, PASSWORD, EMAIL, FIRST_NAME, LAST_NAME, ROLE) " +
								"VALUES ( ?, ?, ?, ?, ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				UserTable.fillPreparedStatement(user, insert);
				insert.executeUpdate();
				ResultSet rs = insert.getGeneratedKeys();
				while (rs.next()){
					user.setId(rs.getInt(1));
				}
			}
			else {
				// update existing user
				query = "UPDATE USER SET USERNAME=?, PASSWORD = ?, EMAIL = ?, FIRST_NAME = ?, LAST_NAME = ?, ROLE = ? " +
						"WHERE ID=?";
				PreparedStatement update = dc.getConnection().prepareStatement(query);
				UserTable.fillPreparedStatement(user, update);
				update.setInt(7, user.getId());
				update.executeUpdate();
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void deleteById(DatabaseConnection db, int id)  {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		try {
			String query = "DELETE FROM USER " + constraints.getParameterString();
			PreparedStatement stmt = db.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static User findByUsernameAndPassword(DatabaseConnection db, String username, String password) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("USERNAME", username));
		constraints.getConstraints().add(new QueryConstraint("PASSWORD", password));
		ArrayList<User> result = UserTable.find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}
	public static User findByNameAndEmail(DatabaseConnection db, String firstName, String lastName, String email) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("LAST_NAME", lastName));
		constraints.getConstraints().add(new QueryConstraint("FIRST_NAME", firstName));
		constraints.getConstraints().add(new QueryConstraint("EMAIL", email));
		ArrayList<User> result = UserTable.find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}
	public static User findByUsername(DatabaseConnection db, String username) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("USERNAME", username));
		ArrayList<User> result = UserTable.find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}

	public static User findById(DatabaseConnection db, int id) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		ArrayList<User> result = UserTable.find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}

	public static Collection<User> findAllByRole(DatabaseConnection db, Role r) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ROLE", r.toString()));
		return UserTable.find(db, constraints);
	}

	public static Collection<User> findAll(DatabaseConnection db){
		QueryConstraints constraints = new QueryConstraints();
		return UserTable.find(db, constraints);
	}

	public static ArrayList<User> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<User> retval = new ArrayList<User>();
		try {
			String query = "SELECT * FROM USER " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				User user = new User();
				UserTable.fillFromResultSet(user, rs);
		        retval.add(user);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}

	public static void fillFromResultSet(User u,ResultSet rs) throws Exception {
		u.setId(rs.getInt("ID"));
		u.setUsername(rs.getString("USERNAME"));
		u.setPassword(rs.getString("PASSWORD"));
		u.setEmail(rs.getString("EMAIL"));
		u.setFirstName(rs.getString("FIRST_NAME"));
		u.setLastName(rs.getString("LAST_NAME"));
		u.setRole(Role.valueOf(rs.getString("ROLE")));
	}

	public static void fillPreparedStatement(User u, PreparedStatement stmt) throws Exception {
		stmt.setString(1, u.getUsername());
		stmt.setString(2, u.getPassword());
		stmt.setString(3, u.getEmail());
		stmt.setString(4, u.getFirstName());
		stmt.setString(5, u.getLastName());
		stmt.setString(6, u.getRole().toString());
	}

}
